/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package scheduleManager;

import app.Global;
import app.MyController;
import app.MyView;
import entities.Employee;
import entities.EmployeeSchedule;
import entities.TimeSlot;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author yoshi
 */
public class ScheduleManagerController implements MyController {

    private long departmentId;
    private AbstractScheduleManager myView;

    public void setDepartmentId(long departmentId) {
        this.departmentId = departmentId;
    }

    public ScheduleManagerController(long departmentId) {
        this.departmentId = departmentId;
    }

    public boolean shiftRequest(EmployeeSchedule schedule, Date date, TimeSlot timeSlot, String note) {
        boolean result = false;
        Connection connection = Global.getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = connection.prepareStatement("SELECT id FROM employee_schedules WHERE date = ? AND time_slot_id = ? AND employee_id = ? AND status = 1");
            st.setDate(1, date);
            st.setLong(2, timeSlot.getId());
            st.setLong(3, Global.getAuth().getId());
            rs = st.executeQuery();
            if (rs.next()) {
                JOptionPane.showMessageDialog(null, "You already have schedule that time", "Something wrong", JOptionPane.WARNING_MESSAGE);
                return false;
            }

            st = connection.prepareStatement("INSERT INTO employee_shift_requests(from_schedule_id, employee_id, request_date, time_slot_id, note, status, created, modified) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
            Timestamp now = new Timestamp(new java.util.Date().getTime());
            st.setLong(1, schedule.getId());
            st.setLong(2, 0);
            st.setDate(3, date);
            st.setLong(4, timeSlot.getId());
            st.setString(5, note);
            st.setLong(6, 1);
            st.setTimestamp(7, now);
            st.setTimestamp(8, now);
            st.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ShiftRequestDialog.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                rs.close();
                st.close();
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(ShiftRequestDialog.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                return result;
            }
        }
    }

    public void deleteSchedule(EmployeeSchedule schedule) {
        Connection connection = Global.getConnection();
        PreparedStatement pst = null;
        try {
            pst = connection.prepareStatement("UPDATE employee_schedules SET status = 0 WHERE id = ?");
            pst.setLong(1, schedule.getId());
            if(pst.executeUpdate() > 0){
                JOptionPane.showMessageDialog(Global.getFrame(), "The schedule has been deleted successfully", "Delete successful", JOptionPane.INFORMATION_MESSAGE);
            }else{
                JOptionPane.showMessageDialog(Global.getFrame(), "The schedule hasn't been deleted successfully", "Delete unsuccessful", JOptionPane.WARNING_MESSAGE);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleManagerController.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                pst.close();
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(ScheduleManagerController.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    @Override
    public AbstractScheduleManager getMyView() {
        return myView;
    }

    @Override
    public ScheduleManagerModel getMyModel() {
        return this.getMyView().getMyModel();
    }

    @Override
    public void setMyView(MyView myView) {
        this.myView = (AbstractScheduleManager) myView;
    }

    public void addSchedule(Date date, TimeSlot timeSlot, Employee employee) {
        Connection connection = Global.getConnection();
        PreparedStatement pst = null;
        

        try {
            // validate add schedule data
            if (timeSlot.getId() == 4) {
                Date afterDate = new Date(date.getTime() + 24 * 3600 * 1000);
                pst = connection.prepareStatement("SELECT id FROM employee_schedules WHERE (date = ? AND time_slot_id = ?) OR (date = ? AND time_slot_id BETWEEN ? AND ?)");
                pst.setDate(1, afterDate);
                pst.setLong(2, 1);
                pst.setDate(3, date);
                pst.setInt(4, 3);
                pst.setInt(5, 4);
            } else if (timeSlot.getId() == 1) {
                Date beforeDate = new Date(date.getTime() - 24 * 3600 * 1000);
                pst = connection.prepareStatement("SELECT id FROM employee_schedules WHERE (date = ? AND time_slot_id = ?) OR (date = ? AND time_slot_id BETWEEN ? AND ?)");
                pst.setDate(1, beforeDate);
                pst.setLong(2, 4);
                pst.setDate(3, date);
                pst.setInt(4, 1);
                pst.setInt(5, 2);
            } else {
                pst = connection.prepareStatement("SELECT id FROM employee_schedules WHERE date = ? AND time_slot_id BETWEEN ? AND ?");
                pst.setDate(1, date);
                pst.setLong(2, timeSlot.getId() - 1);
                pst.setLong(3, timeSlot.getId() + 1);
            }

            Date now = new Date(new java.util.Date().getTime());
            if (pst.executeQuery().next()) {
                JOptionPane.showMessageDialog(Global.getFrame(), "You can not assign schedule here", "Assign error", JOptionPane.ERROR_MESSAGE);
            } else {
                pst.close();
                pst = connection.prepareStatement("INSERT INTO employee_schedules(employee_id, date, time_slot_id, status, created, modified) VALUES(?, ?, ?, ?, ?, ?)");
                pst.setLong(1, employee.getId());
                pst.setDate(2, date);
                pst.setLong(3, timeSlot.getId());
                pst.setInt(4, 1);
                pst.setDate(5, now);
                pst.setDate(6, now);
                pst.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(ScheduleManagerController.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                pst.close();
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(ScheduleManagerController.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                this.getMyView().refresh();
            }
        }
    }
}
